package com.pkdl.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

import com.pkdl.dto.PhieuKhamDTO;

// Data access object, which get data of PhieuKham table from database
public class PhieuKhamDAO {
	public boolean delete(int maphieukham) {
		boolean result = true;
		Connection connect = null;
		try {
			connect = DataAccessHelper.getConnection();
			CallableStatement cs = connect
					.prepareCall("{call usp_PhieuKham_Delete(?)}");
			cs.setInt(1, maphieukham);
			int n = cs.executeUpdate();
			if (n == 0) {
				result = false;
			}
			connect.close();
		} catch (SQLException ex) {
			Logger.getLogger(PhieuKhamDAO.class.getName()).log(Level.SEVERE,
					null, ex);
		} finally {
			try {
				connect.close();
			} catch (SQLException ex) {
				Logger.getLogger(PhieuKhamDAO.class.getName()).log(
						Level.SEVERE, null, ex);
			}
		}
		return result;
	}

	public boolean insert(PhieuKhamDTO phieukham) {
		boolean result = true;
		Connection connect = null;
		try {
			connect = DataAccessHelper.getConnection();
			CallableStatement cs = connect
					.prepareCall("{call usp_PhieuKham_Insert(?, ?, ?, ?, ?, ?, ?, ?)}");
			cs.setInt(1, phieukham.getMaBenhNhan());
			cs.setString(2, phieukham.getMaNguoiLapPhieu());
			cs.setDate(3, new java.sql.Date(phieukham.getNgayKham().getTime()));
			cs.setString(4, phieukham.getKetLuanChung());
			cs.setDate(5, new java.sql.Date(phieukham.getNgayTaiKham()
					.getTime()));
			cs.setString(6, phieukham.getGhiChu());
			cs.setTimestamp(7, new java.sql.Timestamp(phieukham
					.getThoiDiemTiepNhan().getTime()));
			cs.registerOutParameter(8, java.sql.Types.INTEGER);
			int n = cs.executeUpdate();
			if (n == 0) {
				result = false;
			}
			phieukham.setMaPhieuKham(cs.getInt(8));
			connect.close();
		} catch (SQLException ex) {
			Logger.getLogger(PhieuKhamDAO.class.getName()).log(Level.SEVERE,
					null, ex);
		} finally {
			try {
				connect.close();
			} catch (SQLException ex) {
				Logger.getLogger(PhieuKhamDAO.class.getName()).log(
						Level.SEVERE, null, ex);
			}
		}
		return result;
	}

	public ArrayList<PhieuKhamDTO> selectAll() {
		ArrayList<PhieuKhamDTO> list = new ArrayList<PhieuKhamDTO>();
		Connection connect = null;
		try {
			connect = DataAccessHelper.getConnection();
			CallableStatement cs = connect
					.prepareCall("{call usp_PhieuKham_SelectAll()}");
			ResultSet rs = cs.executeQuery();
			while (rs.next()) {
				PhieuKhamDTO phieukham = new PhieuKhamDTO();
				phieukham.setMaPhieuKham(rs.getInt("MaPhieuKham"));
				phieukham.setMaBenhNhan(rs.getInt("MaBenhNhan"));
				phieukham.setTenBenhNhan(rs.getString("TenBenhNhan"));
				phieukham.setMaNguoiLapPhieu(rs.getString("MaNguoiLapPhieu"));
				phieukham.setTenNguoiLapPhieu(rs.getString("TenNguoiLapPhieu"));
				phieukham.setNgayKham(rs.getDate("NgayKham"));
				phieukham.setKetLuanChung(rs.getString("KetLuanChung"));
				phieukham.setNgayTaiKham(rs.getDate("NgayTaiKham"));
				phieukham.setGhiChu(rs.getString("GhiChu"));
				phieukham.setThoiDiemTiepNhan(rs
						.getTimestamp("ThoiDiemTiepNhan"));
				list.add(phieukham);
			}
			connect.close();
		} catch (SQLException ex) {
			Logger.getLogger(PhieuKhamDAO.class.getName()).log(Level.SEVERE,
					null, ex);
		} finally {
			try {
				connect.close();
			} catch (SQLException ex) {
				Logger.getLogger(PhieuKhamDAO.class.getName()).log(
						Level.SEVERE, null, ex);
			}
		}
		return list;
	}

	public PhieuKhamDTO selectById(int maphieukham) {
		PhieuKhamDTO phieukham = null;
		Connection connect = null;
		try {
			connect = DataAccessHelper.getConnection();
			CallableStatement cs = connect
					.prepareCall("{call usp_PhieuKham_SelectByID(?)}");
			cs.setInt(1, maphieukham);
			ResultSet rs = cs.executeQuery();
			while (rs.next()) {
				phieukham = new PhieuKhamDTO();
				phieukham.setMaPhieuKham(rs.getInt("MaPhieuKham"));
				phieukham.setMaBenhNhan(rs.getInt("MaBenhNhan"));
				phieukham.setMaNguoiLapPhieu(rs.getString("MaNguoiLapPhieu"));
				phieukham.setNgayKham(rs.getDate("NgayKham"));
				phieukham.setKetLuanChung(rs.getString("KetLuanChung"));
				phieukham.setNgayTaiKham(rs.getDate("NgayTaiKham"));
				phieukham.setGhiChu(rs.getString("GhiChu"));
				phieukham.setThoiDiemTiepNhan(rs
						.getTimestamp("ThoiDiemTiepNhan"));
			}
			connect.close();
		} catch (SQLException ex) {
			Logger.getLogger(PhieuKhamDAO.class.getName()).log(Level.SEVERE,
					null, ex);
		} finally {
			try {
				connect.close();
			} catch (SQLException ex) {
				Logger.getLogger(PhieuKhamDAO.class.getName()).log(
						Level.SEVERE, null, ex);
			}
		}
		return phieukham;
	}

	public ArrayList<PhieuKhamDTO> selectByMaBenhNhan(int mabenhnhan) {
		ArrayList<PhieuKhamDTO> list = new ArrayList<PhieuKhamDTO>();
		Connection connect = null;
		try {
			connect = DataAccessHelper.getConnection();
			CallableStatement cs = connect
					.prepareCall("{call usp_PhieuKham_SelectByMaBenhNhan(?)}");
			cs.setInt(1, mabenhnhan);
			ResultSet rs = cs.executeQuery();
			while (rs.next()) {
				PhieuKhamDTO phieukham = new PhieuKhamDTO();
				phieukham.setMaPhieuKham(rs.getInt("MaPhieuKham"));
				phieukham.setMaBenhNhan(rs.getInt("MaBenhNhan"));
				phieukham.setMaNguoiLapPhieu(rs.getString("MaNguoiLapPhieu"));
				phieukham.setNgayKham(rs.getDate("NgayKham"));
				phieukham.setKetLuanChung(rs.getString("KetLuanChung"));
				phieukham.setNgayTaiKham(rs.getDate("NgayTaiKham"));
				phieukham.setGhiChu(rs.getString("GhiChu"));
				phieukham.setThoiDiemTiepNhan(rs
						.getTimestamp("ThoiDiemTiepNhan"));
				list.add(phieukham);
			}
			connect.close();
		} catch (SQLException ex) {
			Logger.getLogger(PhieuKhamDAO.class.getName()).log(Level.SEVERE,
					null, ex);
		} finally {
			try {
				connect.close();
			} catch (SQLException ex) {
				Logger.getLogger(PhieuKhamDAO.class.getName()).log(
						Level.SEVERE, null, ex);
			}
		}
		return list;
	}

	public ArrayList<PhieuKhamDTO> selectByMaNguoiLapPhieu(
			String manguoilapphieu) {
		ArrayList<PhieuKhamDTO> list = new ArrayList<PhieuKhamDTO>();
		Connection connect = null;
		try {
			connect = DataAccessHelper.getConnection();
			CallableStatement cs = connect
					.prepareCall("{call usp_PhieuKham_SelectByMaNguoiLapPhieu(?)}");
			cs.setString(1, manguoilapphieu);
			ResultSet rs = cs.executeQuery();
			while (rs.next()) {
				PhieuKhamDTO phieukham = new PhieuKhamDTO();
				phieukham.setMaPhieuKham(rs.getInt("MaPhieuKham"));
				phieukham.setMaBenhNhan(rs.getInt("MaBenhNhan"));
				phieukham.setMaNguoiLapPhieu(rs.getString("MaNguoiLapPhieu"));
				phieukham.setNgayKham(rs.getDate("NgayKham"));
				phieukham.setKetLuanChung(rs.getString("KetLuanChung"));
				phieukham.setNgayTaiKham(rs.getDate("NgayTaiKham"));
				phieukham.setGhiChu(rs.getString("GhiChu"));
				phieukham.setThoiDiemTiepNhan(rs
						.getTimestamp("ThoiDiemTiepNhan"));
				list.add(phieukham);
			}
			connect.close();
		} catch (SQLException ex) {
			Logger.getLogger(PhieuKhamDAO.class.getName()).log(Level.SEVERE,
					null, ex);
		} finally {
			try {
				connect.close();
			} catch (SQLException ex) {
				Logger.getLogger(PhieuKhamDAO.class.getName()).log(
						Level.SEVERE, null, ex);
			}
		}
		return list;
	}

	public PhieuKhamDTO selectByThoiDiemTiepNhan(Timestamp date) {
		PhieuKhamDTO phieukham = null;
		Connection connect = null;
		try {
			connect = DataAccessHelper.getConnection();
			CallableStatement cs = connect
					.prepareCall("{call usp_PhieuKham_SelectByThoiDiemTiepNhan(?)}");
			cs.setTimestamp(1, date);
			ResultSet rs = cs.executeQuery();
			while (rs.next()) {
				phieukham = new PhieuKhamDTO();
				phieukham.setMaPhieuKham(rs.getInt("MaPhieuKham"));
				phieukham.setMaBenhNhan(rs.getInt("MaBenhNhan"));
				phieukham.setMaNguoiLapPhieu(rs.getString("MaNguoiLapPhieu"));
				phieukham.setNgayKham(rs.getDate("NgayKham"));
				phieukham.setKetLuanChung(rs.getString("KetLuanChung"));
				phieukham.setNgayTaiKham(rs.getDate("NgayTaiKham"));
				phieukham.setGhiChu(rs.getString("GhiChu"));
				phieukham.setThoiDiemTiepNhan(rs
						.getTimestamp("ThoiDiemTiepNhan"));
			}
			connect.close();
		} catch (SQLException ex) {
			Logger.getLogger(PhieuKhamDAO.class.getName()).log(Level.SEVERE,
					null, ex);
		} finally {
			try {
				connect.close();
			} catch (SQLException ex) {
				Logger.getLogger(PhieuKhamDAO.class.getName()).log(
						Level.SEVERE, null, ex);
			}
		}
		return phieukham;
	}

	public boolean update(PhieuKhamDTO phieukham) {
		boolean result = true;
		Connection connect = null;
		try {
			connect = DataAccessHelper.getConnection();
			CallableStatement cs = connect
					.prepareCall("{call usp_PhieuKham_Update(?, ?, ?, ?, ?, ?, ?)}");
			cs.setInt(1, phieukham.getMaPhieuKham());
			cs.setInt(2, phieukham.getMaBenhNhan());
			cs.setString(3, phieukham.getMaNguoiLapPhieu());
			cs.setDate(4, new java.sql.Date(phieukham.getNgayKham().getTime()));
			cs.setString(5, phieukham.getKetLuanChung());
			cs.setDate(6, new java.sql.Date(phieukham.getNgayTaiKham()
					.getTime()));
			cs.setString(7, phieukham.getGhiChu());
			int n = cs.executeUpdate();
			if (n == 0) {
				result = false;
			}
			connect.close();
		} catch (SQLException ex) {
			Logger.getLogger(PhieuKhamDAO.class.getName()).log(Level.SEVERE,
					null, ex);
		} finally {
			try {
				connect.close();
			} catch (SQLException ex) {
				Logger.getLogger(PhieuKhamDAO.class.getName()).log(
						Level.SEVERE, null, ex);
			}
		}
		return result;
	}

}
